Skip to main content Skip to complementary content

Creating and managing custom ETLs

In addition to the Mappings ETL, you can define custom ETLs as required. User-defined ETLs can perform a number of useful operations such as defining specific transformations, gathering statistics, performing cleansing, and filtering data.

Information noteCommon Table Expressions (CTEs) are not supported as well as some special clauses.
  1. Click the Manage button in the bottom left of the Data Warehouse panel. The Manage Data Warehouse Tasks window opens.

  2. Select one of the following tabs according to your needs:

    • Pre Loading ETL - to define an ETL that will manipulate the data before it is loaded from the landing tables to the data warehouse staging tables. When enabled, the Pre-loading ETL will be run even if there are no mappings or Replicate-generated source data associated with it, which is particularly useful for customer wanting to perform transformations on data generated by third-party tools.
    • Multi Table ETL - to define an ETL for multiple tables.
    • Single Table ETL - to define an ETL for a single table.
    • Post Loading ETL - to define an ETL that will be executed after the data has been loaded from the staging tables to the data warehouse.
  3. If you selected Single Table ETL, select an entity in the Entity column and then click the New button above the Entity list. For Multi Table and Post Loading ETLs, just click the New button.
  4. Specify a name for your ETL and then click OK.

    If you selected Single Table ETL, the ETL is added as a link to the User Defined ETL column. If you selected Multi Table ETL or Post Loading ETL, the ETL is added as a link in their respective tabs.

  5. Click the link to open the Edit ETL Instructions window.
  6. If you selected Single Table ETL, select a column and click the arrow to the right of the selected column to add it to the ETL.

    If you selected Multi Table ETL or Post Loading ETL, select a table and a column and then click the arrow to the right of the selected table/column to add it to the ETL. Repeat as necessary.

  7. Use the Select, Delete, Insert and Update toolbar buttons at the top of the window to add SQL statements to your ETL.
  8. To run the ETL as a stored procedure (that already exists in the data warehouse):
    1. Select the Execute as Stored Procedure check box.
    2. Click the Stored Procedure toolbar button.
    3. Replace STORED_PROCEDURE with the name of your stored procedure and replace(PARAM1, PARAM2) with any parameters that it needs. Note that parameters must be separated by a comma. If no parameters are required, use empty parenthesis or drop them altogether.
  9. Use the Undo, Redo and Reset buttons at the bottom of the window if needed.
  10. Optionally, specify a description in the Description box at the bottom of the window.
  11. To save your ETL, click OK.

Single table example

The following example, based on the Data warehouse project tutorial in Getting started with Data Warehouse projects , demonstrates how to concatenate two columns called "First Name" and "Last Name" into a single column called "FullName".

  1. Click the Manage button in the Model panel. The Manage Model window opens.

  2. Select Employees from the Entities list on the left.
  3. Click the + (plus) toolbar button to add a new Attribute. A new row is added to the Attributes table.

  4. Type any letter in the Column Name column to bring up the "Add New" option. Click the "Add New" option when it appears.

    Add New option visible in dropdown list after text entered in Column Name column

    The New Attribute Domain window opens.

  5. In the Name field, type FullName. From the Type drop-down list, select Varchar. In the Length field, enter 100.
  6. In the History column, select Type 1 from the drop-down list.
  7. Click OK to close the New Attribute Domain window and add the attribute to the Attributes table.
  8. Then click OK again (below the newly added attribute) to exit edit mode.

    Image depicting OK button below newly added attribute

  9. Close the Edit Model window.
  10. In the Data Warehouse panel, click the Create button.
  11. After the Data Warehouse tables have been created, close the Creating Data Warehouse window.
  12. Click the Manage button in the bottom left corner of the Data Warehouse panel. The Manage Data Warehouse Tasks window opens.

  13. To view the current mappings between the source columns and data warehouse columns, click the Map_Employees_1 link in the Mappings column. A "Processing" icon is displayed while the mappings are generated. After the mappings are generated, the Edit Mappings - Map_Employees_1 window opens automatically.

    Note that the FullName column has been added to the data warehouse columns, but is currently not mapped to the source columns.

    Image depicting FullName column on the right side of the interface with data warehouse columns but unlinked to source columns on the left side

  14. The next stage is to define an ETL that will map the First Name and Last Name source columns to the Full Name data warehouse column.
  15. Close the Edit Mappings - Map_Employees_1 window and then select the Single Table ETL tab on the left.
  16. Select Employees in the Entity column and then click the New button above the column. The Add New Single Table ETL window opens.

  17. Specify a name or leave the default name and then click OK.
  18. Click the Edit button (represented by a pencil icon) at the end of the Employees row. The Edit Single Table ETL: <Name> window opens.

  19. In the editing pane on the right, enter the following instruction:

    UPDATE dbo.TSTG_EMPLOYEES set

    FullName = LASTNAME + FIRSTNAME

  20. Click OK to save the ETL and close the window.

    Information note

    After Compose has finished populating the Data Warehouse, you can open the table in Microsoft SQL Server Management Studio and verify that the new column has been added with the correct data.

Updating custom ETLs

Information note

Compose CLI requires Administrator permission. To grant Administrator permission, select "Run as administrator" when opening the command prompt. All commands should be run from the Compose bin directory (C:\Program Files\Qlik\Compose with a default installation).

You can update custom ETLs using the Compose CLI. This functionality can be incorporated into a script to easily update Custom ETLs.

Syntax:

composecli update_custom_etls --project name --infolder path

Where:

  • project is the name of the project with the custom ETLs you want to update
  • infolder is the full path to the folder containing the custom ETL files

Example:

composecli update_custom_etls --project my-project --infolder c:\Compose\CustomETLs

Warning noteThe file names in the input folder must be identical to the custom ETL names in the specified project. Otherwise, an error will occur. The file extension (for example, .txt) is not important, but the file must be in SQL format.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!